package cn.exrick.xboot.modules.ReportExcel;

import cn.exrick.xboot.base.XbootBaseController;
import cn.exrick.xboot.base.XbootBaseService;
import cn.exrick.xboot.common.utils.LetterConvert;
import cn.exrick.xboot.common.utils.ResultUtil;
import cn.exrick.xboot.common.vo.Result;
import cn.exrick.xboot.flow.VO.FlowNodeinfoVo;
import cn.exrick.xboot.flow.entity.FlowNodeinfo;
import cn.exrick.xboot.flow.entity.FlowTemplateinfo;
import cn.exrick.xboot.flow.entity.FlowUserinfo;
import cn.exrick.xboot.flow.service.IFlowNodeinfoService;
import cn.exrick.xboot.flow.service.IFlowTemplateinfoService;
import cn.exrick.xboot.modules.assessment.service.IHkAssessmentScoreService;
import cn.exrick.xboot.modules.assessment.vo.*;
import cn.exrick.xboot.modules.base.entity.Department;
import cn.exrick.xboot.modules.base.utils.DateUtils;
import cn.exrick.xboot.modules.content.entity.*;
import cn.exrick.xboot.modules.content.service.*;
import cn.exrick.xboot.modules.content.service.mybatis.IHontUserService;
import cn.exrick.xboot.modules.workovertime.VO.HkOvertimeByPageVo;
import cn.exrick.xboot.modules.workovertime.VO.HkOvertimeInfoNameAndUserNickVo;
import cn.exrick.xboot.modules.workovertime.VO.HkOvertimeSummarizingAll;
import cn.exrick.xboot.modules.workovertime.VO.HkOvertimeSummarizingByMonthVo;
import cn.exrick.xboot.modules.workovertime.entity.HkOvertimeCollect;
import cn.exrick.xboot.modules.workovertime.mapper.HkOvertimeCollectMapper;
import cn.exrick.xboot.modules.workovertime.service.IHkOvertimeDetailService;
import cn.hutool.core.util.RandomUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.collect.Lists;
import com.spire.xls.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletResponse;
import javax.swing.*;
import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import static java.util.stream.Collectors.groupingBy;


@Slf4j
@RequestMapping("/xboot/report")
@Controller
public class OutputCotroller  extends XbootBaseController {


    @Autowired
    private IHkAssessmentScoreService iHkAssessmentScoreService;
    @Autowired
    private HontUserService hontUserService;
    @Autowired
    private IHontUserService iHontUserService;
    @Autowired
    private HontDepartmentService hontDepartmentService;
    @Autowired
    private HontPostService hontPostService;
    @Autowired
    private HontUserPostService hontUserPostService;
    @Autowired
    private IFlowNodeinfoService iFlowNodeinfoService;
    @Autowired
    public IFlowTemplateinfoService iFlowTemplateinfoService;
    @Autowired
    private HontRoleService hontRoleService;
    @Autowired
    private IHkOvertimeDetailService hkOvertimeDetailService;
    @Autowired
    private HkOvertimeCollectMapper hkOvertimeCollectMapper;
    @Override
    public XbootBaseService getService() {
        return null;
    }

    @RequestMapping(value = "/byDepartmentId", method = RequestMethod.GET)
    @ResponseBody
    public Result<Object> selectByDepartmentId(String departmentId, String beginDate, String endDate,HttpServletResponse response) throws IOException {
        List<Map<String, Object>> maps = iHkAssessmentScoreService.selectScoreByHontUserOnDepartement(departmentId, beginDate, endDate);
        if (maps.size() > 0){
            return ResultUtil.success("正在下载");
        }
        return ResultUtil.error("暂无数据，请重新选择!");
    }
    /**
     * 测试生成excel表格
     */
    @RequestMapping(value = "/testhh/download", method = RequestMethod.GET)
    @ResponseBody
    public void downloadFile(String departmentId, String beginDate, String endDate, HttpServletResponse response) throws IOException {

        List<Map<String, Object>> maps = iHkAssessmentScoreService.selectScoreByHontUserOnDepartement(departmentId, beginDate, endDate);
        List<Map<String, Object>> plusMap = iHkAssessmentScoreService.selectPlusByHontUserOnDepartementId(beginDate, endDate, departmentId);
        List<Map<String, Object>> minusMap = iHkAssessmentScoreService.selectMinusByHontUserOnDepartementId(beginDate, endDate, departmentId);
        List<Map<String, Object>> byHontUserOnDepartementId = iHkAssessmentScoreService.selectByHontUserOnDepartementId(beginDate, endDate, departmentId);
        int countMap = iHkAssessmentScoreService.selectCountByHontUserOnDepartementId(beginDate, endDate, departmentId);

        HontDepartment hontDepartment1 = hontDepartmentService.get(departmentId);
        hontDepartment1.getTitle();
        String filename = hontDepartment1.getTitle() + "绩效考核记录表.xlsx";

        List<Map<String,String>> data = new ArrayList<>();
        List<Map<String,String>> dataTo = new ArrayList<>();
        for (int i = 0; i < maps.size(); i++){
            Map<String,String> userlList = new LinkedHashMap<>();
            Map<String,String> userlListTo = new LinkedHashMap<>();
            String id = maps.get(i).get("id").toString();
            String index_name = maps.get(i).get("describes").toString().replace(",", "<br>");
            String excelrow = index_name.replace("<br>", "EXCELROW");
            String excelrow1 = excelrow.replace("EXCELROW", "\n");
            String commented = maps.get(i).get("commented").toString();
            int lastIndexOf = commented.lastIndexOf(",");
            String commenteds = commented.substring((lastIndexOf + 1), commented.length());

            Float score = Float.valueOf(maps.get(i).get("score").toString());
            HontUser user1 = hontUserService.get(id);
            HontDepartment hontDepartment = hontDepartmentService.get(user1.getDepartmentId());
            List<HontUserPost> byHontUserPost = hontUserPostService.findByHontUserId(user1.getId());
            List<String> postIds = new ArrayList<>();
            if (byHontUserPost.size() > 0) {
                for (int p = 0; p < byHontUserPost.size(); p++) {
                    HontPost hontPost = hontPostService.get(byHontUserPost.get(p).getHontPostId());
                    postIds.add(hontPost.getTitle());
                }
            }
            String collect = "";
            if (postIds.size() > 0){
                collect = postIds.stream().collect(Collectors.joining(","));
            }

            userlList.put("id",String.valueOf(i+1));
            userlList.put("name",user1.getNickName());
            userlList.put("department",hontDepartment.getTitle());
            userlList.put("post",collect);
            userlList.put("score",String.valueOf(score));
            for (int j = 0; j < plusMap.size(); j++) {
                if (plusMap.get(j).get("id").toString().equals(id)){
                    userlList.put("plus",plusMap.get(j).get("counts").toString());
                    break;
                }else {
                    userlList.put("plus","0");
                }
            }
            for (int y = 0; y < minusMap.size(); y++) {
                if (minusMap.get(y).get("id").toString().equals(id)){
                    userlList.put("minus",minusMap.get(y).get("counts").toString());
                    break;
                }else {
                    userlList.put("minus","0");
                }
            }
            userlList.put("count",byHontUserOnDepartementId.get(i).get("counts").toString());

            userlListTo.put("id",String.valueOf(i+1));
            userlListTo.put("name",user1.getNickName());
            userlListTo.put("department",hontDepartment.getTitle());
            userlListTo.put("post",collect);
            userlListTo.put("score",String.valueOf(score));
            userlListTo.put("index",excelrow1);
            userlListTo.put("commented",commenteds);
            data.add(userlList);
            dataTo.add(userlListTo);
        }
        data.get(0).put("counts",String.valueOf(countMap));
        String[] headers = { "序号", "姓名", "部门", "岗位","考核结果","加分次数","扣分次数","共计次数","考核总次数" };
        String fileName = "汇总表";
        String[] headers2 = { "序号", "姓名", "部门", "岗位","考核结果","考核详情","备注" };
        String fileName2 = "明细表";
        ExportExcelUtile eeu = new ExportExcelUtile();
        XSSFWorkbook workbook = new XSSFWorkbook();
        eeu.exportExcel(workbook,0,fileName,headers,data);
        eeu.exportExcel(workbook,1,fileName2,headers2,dataTo);
        response.setContentType("application/octet-stream");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "ISO8859-1"));
        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());

        //关闭workbook
        workbook.close();
    }


    /**
     * 测试生成excel表格
     */
    @RequestMapping(value = "/downloadModel", method = RequestMethod.GET)
    @ResponseBody
    public void downloadModel( HttpServletResponse response) throws IOException {

        List<HontDepartment> all = hontDepartmentService.getAll();
        if (all.size()> 0 ){
            String filename = "新增用户模板.xlsx";
            List<Map<String,String>> dataTo = new ArrayList<>();
            for (int i = 0; i < all.size(); i++) {
            Map<String,String> userlListTo = new LinkedHashMap<>();
            userlListTo.put("部门",all.get(i).getTitle());
            userlListTo.put("id",all.get(i).getId());
            dataTo.add(userlListTo);
            }

            List<Map<String,String>> data = new ArrayList<>();


            String[] headers = { "姓名", "手机号", "角色", "部门","岗位" };
            String fileName = "用户信息";
            String[] headers2 = { "部门名称", "id"};
            String fileName2 = "部门对应id";
            ExportExcelUtile eeu = new ExportExcelUtile();
            XSSFWorkbook workbook = new XSSFWorkbook();
            eeu.exportExcel(workbook,0,fileName,headers,null);
            eeu.exportExcel(workbook,1,fileName2,headers2,dataTo);

            response.setContentType("application/octet-stream");

            //设置导出Excel的名称
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312")));
            //刷新缓冲
            response.flushBuffer();

            //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
            workbook.write(response.getOutputStream());

            //关闭workbook
            workbook.close();
        }
    }

    /**
     * 员工考核明细表1.6
     */
    @RequestMapping(value = "/getHkAssessmentScoreGroupByUser", method = RequestMethod.GET)
    @ResponseBody
    public void getHkAssessmentScoreGroupByUser(String departmentId, String beginDate, String endDate,String flowTemplateId,HttpServletResponse response) throws IOException, InterruptedException {

        String[] departmentIds = departmentId.split(",");
        if (departmentIds.length > 1){
            List<File> fileList = new ArrayList<>();
            for (int i = 0; i < departmentIds.length; i++) {
                OutPutAssessmentVo outPutAssessmentVo = new OutPutAssessmentVo();
                outPutAssessmentVo.setEndDate(endDate);
                outPutAssessmentVo.setBeginDate(beginDate);
                outPutAssessmentVo.setDepartmentId(departmentIds[i]);
                outPutAssessmentVo.setBusinessType(flowTemplateId);
                List<HontUser> byDepartmentId = hontUserService.getAll();
                List<HontUser> byUserDepartmentId = hontUserService.findByDepartmentId(departmentIds[i]);
                HontRole byName = hontRoleService.findByName("员工");
                HontRole monitor = hontRoleService.findByName("班长");
                Integer monitorCountByDep = iHontUserService.getUserRoleCountByDep(departmentIds[i], monitor.getId());
                Integer staffCountByDep = iHontUserService.getUserRoleCountByDep(departmentIds[i], byName.getId());
                Map<String,Integer> userRoleCount = new HashMap<>();
                userRoleCount.put("all",byUserDepartmentId.size());
                userRoleCount.put("monitor",monitorCountByDep);
                userRoleCount.put("staff",staffCountByDep);
                userRoleCount.put("other",byUserDepartmentId.size() - monitorCountByDep - staffCountByDep);
                HontDepartment hontDepartment1 = hontDepartmentService.get(departmentIds[i]);
                String filename = hontDepartment1.getTitle() + "绩效考核记录表.xlsx";
                List<HkAssessmentScoreGroupByUser> hkAssessmentScoreGroupByUsers = iHkAssessmentScoreService.selectHkAssessmentScoreGroupByUser(beginDate, endDate, departmentIds[i],flowTemplateId);
                List<HkAssessmentDetailVo> hkAssessmentDetailVos = iHkAssessmentScoreService.selectHkAssessmentDetailVo(beginDate, endDate, departmentIds[i],flowTemplateId);
                List<HkAssessmentScoreCountDataVo> hkAssessmentScoreCountData = iHkAssessmentScoreService.getHkAssessmentScoreCountData(beginDate, endDate, departmentIds[i], flowTemplateId);
                List<HkAssessmentScorePlusOrMinusVo> hkAssessmentScorePlus = iHkAssessmentScoreService.getHkAssessmentScorePlus(beginDate, endDate, departmentIds[i], flowTemplateId);
                List<HkAssessmentScorePlusOrMinusVo> hkAssessmentScoreMinus = iHkAssessmentScoreService.getHkAssessmentScoreMinus(beginDate, endDate, departmentIds[i], flowTemplateId);
                if (hkAssessmentScoreCountData.size() > 0){
                    for (int s = 0; s < hkAssessmentScoreCountData.size(); s++) {
                        if (hkAssessmentScorePlus.size() > 0){
                            for (int j = 0; j < hkAssessmentScorePlus.size(); j++) {
                                if (hkAssessmentScorePlus.get(j).getId().equals(hkAssessmentScoreCountData.get(s).getId())) {
                                    hkAssessmentScoreCountData.get(s).setPlus(hkAssessmentScorePlus.get(j).getCountNumber());
                                    break;
                                } else {
                                    hkAssessmentScoreCountData.get(s).setPlus(0);
                                }
                            }
                        }else {
                            hkAssessmentScoreCountData.get(s).setPlus(0);
                        }
                        if (hkAssessmentScoreMinus.size() > 0){
                            for (int m = 0; m < hkAssessmentScoreMinus.size(); m++) {
                                if (hkAssessmentScoreMinus.get(m).getId().equals(hkAssessmentScoreCountData.get(s).getId())) {
                                    hkAssessmentScoreCountData.get(s).setMinus(hkAssessmentScoreMinus.get(m).getCountNumber());
                                    break;
                                } else {
                                    hkAssessmentScoreCountData.get(s).setMinus(0);
                                }
                            }
                        }else {
                            hkAssessmentScoreCountData.get(s).setMinus(0);
                        }

                    }
                }


                log.info("汇总：{}",hkAssessmentScoreCountData);
                List<String> scoreIds = new ArrayList<String>();
                hkAssessmentDetailVos.stream().forEach(hkAssessmentDetailVo -> {
                    scoreIds.add(hkAssessmentDetailVo.getPrId());

                });

                List<SubmitUserVo> submitUserDetail = iHkAssessmentScoreService.getSubmitUserDetail(scoreIds);
                List<ApproverDetailVo> approverDetailVo = new ArrayList<>();
                scoreIds.forEach(s -> {
                    List<ApproverDetailVo> approverDetail = iHkAssessmentScoreService.getApproverDetail(s);
                    List<ApproverDetailVo> approverDetails = getApproverDetailVo(approverDetail);
                    approverDetailVo.addAll(approverDetails);
                });

                hkAssessmentDetailVos.stream().map(m -> {
                    submitUserDetail.stream().filter(m2-> Objects.equals(m.getPrId(),m2.getPid())).forEach(m2-> {
                        m.setSubmitUser(m2.getNickName());
                        m.setSubmitTime(m2.getSubmitTime());
                        m.setSubmitDescribe(m2.getSubmitDescribe());
                    });
                    byDepartmentId.stream().filter(u-> Objects.equals(m.getApprovalUserId(),u.getId())).forEach(u-> {
                        m.setApprovalUserName(u.getNickName());
                    });
                    return m;
                }).collect(Collectors.toList());


                hkAssessmentDetailVos.forEach(hkAssessmentDetailVo -> {
                    String datePoor = DateUtils.getDatePoor(hkAssessmentDetailVo.getApprovalTime(), hkAssessmentDetailVo.getSubmitTime());
                    hkAssessmentDetailVo.setTimeDifference(datePoor);
                    List<ApproverDetailVo> collect = approverDetailVo.stream().filter(approverDetailVo1 ->
                            approverDetailVo1.getPid().equals(hkAssessmentDetailVo.getPrId())).collect(Collectors.toList());
                    hkAssessmentDetailVo.setApproverDetailVos(collect);
                });
                List<String> flowIds = hkAssessmentScoreGroupByUsers.stream()
                        .collect(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(HkAssessmentScoreGroupByUser::getFlowTemplateId))))
                        .stream()
                        .map(HkAssessmentScoreGroupByUser::getFlowTemplateId).filter(flowId -> flowId != null && !flowId.isEmpty()).collect(Collectors.toList());

                String chart = createChart(hkAssessmentScoreGroupByUsers,outPutAssessmentVo, hkAssessmentDetailVos, flowIds,approverDetailVo, hkAssessmentScoreCountData, userRoleCount, filename, response);
                fileList.add(new File(chart));
            }
            zipFiles(fileList,response);
        }else if (departmentIds.length == 1){
            for (int i = 0; i < departmentIds.length; i++) {
                OutPutAssessmentVo outPutAssessmentVo = new OutPutAssessmentVo();
                outPutAssessmentVo.setEndDate(endDate);
                outPutAssessmentVo.setBeginDate(beginDate);
                outPutAssessmentVo.setDepartmentId(departmentIds[i]);
                outPutAssessmentVo.setBusinessType(flowTemplateId);
                List<HontUser> byDepartmentId = hontUserService.getAll();
                List<HontUser> byUserDepartmentId = hontUserService.findByDepartmentId(departmentIds[i]);
                HontRole byName = hontRoleService.findByName("员工");
                HontRole monitor = hontRoleService.findByName("班长");
                Integer monitorCountByDep = iHontUserService.getUserRoleCountByDep(departmentIds[i], monitor.getId());
                Integer staffCountByDep = iHontUserService.getUserRoleCountByDep(departmentIds[i], byName.getId());
                Map<String, Integer> userRoleCount = new HashMap<>();
                userRoleCount.put("all", byUserDepartmentId.size());
                userRoleCount.put("monitor", monitorCountByDep);
                userRoleCount.put("staff", staffCountByDep);
                userRoleCount.put("other", byUserDepartmentId.size() - monitorCountByDep - staffCountByDep);
                HontDepartment hontDepartment1 = hontDepartmentService.get(departmentIds[i]);
                String filename = hontDepartment1.getTitle() + "绩效考核记录表.xlsx";
                List<HkAssessmentScoreGroupByUser> hkAssessmentScoreGroupByUsers = iHkAssessmentScoreService.selectHkAssessmentScoreGroupByUser(beginDate, endDate, departmentIds[i], flowTemplateId);
                List<HkAssessmentDetailVo> hkAssessmentDetailVos = iHkAssessmentScoreService.selectHkAssessmentDetailVo(beginDate, endDate, departmentIds[i], flowTemplateId);
                List<HkAssessmentScoreCountDataVo> hkAssessmentScoreCountData = iHkAssessmentScoreService.getHkAssessmentScoreCountData(beginDate, endDate, departmentIds[i], flowTemplateId);
                List<HkAssessmentScorePlusOrMinusVo> hkAssessmentScorePlus = iHkAssessmentScoreService.getHkAssessmentScorePlus(beginDate, endDate, departmentIds[i], flowTemplateId);
                List<HkAssessmentScorePlusOrMinusVo> hkAssessmentScoreMinus = iHkAssessmentScoreService.getHkAssessmentScoreMinus(beginDate, endDate, departmentIds[i], flowTemplateId);
                if (hkAssessmentScoreCountData.size() > 0){
                    for (int s = 0; s < hkAssessmentScoreCountData.size(); s++) {
                        if (hkAssessmentScorePlus.size() > 0){
                            for (int j = 0; j < hkAssessmentScorePlus.size(); j++) {
                                if (hkAssessmentScorePlus.get(j).getId().equals(hkAssessmentScoreCountData.get(s).getId())) {
                                    hkAssessmentScoreCountData.get(s).setPlus(hkAssessmentScorePlus.get(j).getCountNumber());
                                    break;
                                } else {
                                    hkAssessmentScoreCountData.get(s).setPlus(0);
                                }
                            }
                        }else {
                            hkAssessmentScoreCountData.get(s).setPlus(0);
                        }
                        if (hkAssessmentScoreMinus.size() > 0){
                            for (int m = 0; m < hkAssessmentScoreMinus.size(); m++) {
                                if (hkAssessmentScoreMinus.get(m).getId().equals(hkAssessmentScoreCountData.get(s).getId())) {
                                    hkAssessmentScoreCountData.get(s).setMinus(hkAssessmentScoreMinus.get(m).getCountNumber());
                                    break;
                                } else {
                                    hkAssessmentScoreCountData.get(s).setMinus(0);
                                }
                            }
                        }else {
                            hkAssessmentScoreCountData.get(s).setMinus(0);
                        }

                    }
                }



                log.info("汇总：{}", hkAssessmentScoreCountData);
                List<String> scoreIds = new ArrayList<String>();
                hkAssessmentDetailVos.stream().forEach(hkAssessmentDetailVo -> {
                    SubmitUserVo vo = new SubmitUserVo();
                    scoreIds.add(hkAssessmentDetailVo.getPrId());

                });

                List<SubmitUserVo> submitUserDetail = iHkAssessmentScoreService.getSubmitUserDetail(scoreIds);
                List<ApproverDetailVo> approverDetailVo = new ArrayList<>();
                scoreIds.forEach(s -> {
                    List<ApproverDetailVo> approverDetail = iHkAssessmentScoreService.getApproverDetail(s);
                    List<ApproverDetailVo> approverDetails = getApproverDetailVo(approverDetail);
                    approverDetailVo.addAll(approverDetails);
                });

                hkAssessmentDetailVos.stream().map(m -> {
                    submitUserDetail.stream().filter(m2 -> Objects.equals(m.getPrId(), m2.getPid())).forEach(m2 -> {
                        m.setSubmitUser(m2.getNickName());
                        m.setSubmitTime(m2.getSubmitTime());
                        m.setSubmitDescribe(m2.getSubmitDescribe());
                    });
                    byDepartmentId.stream().filter(u -> Objects.equals(m.getApprovalUserId(), u.getId())).forEach(u -> {
                        m.setApprovalUserName(u.getNickName());
                    });
                    return m;
                }).collect(Collectors.toList());


                hkAssessmentDetailVos.forEach(hkAssessmentDetailVo -> {
                    String datePoor = DateUtils.getDatePoor(hkAssessmentDetailVo.getApprovalTime(), hkAssessmentDetailVo.getSubmitTime());
                    hkAssessmentDetailVo.setTimeDifference(datePoor);
                    List<ApproverDetailVo> collect = approverDetailVo.stream().filter(approverDetailVo1 ->
                            approverDetailVo1.getPid().equals(hkAssessmentDetailVo.getPrId())).collect(Collectors.toList());
                    hkAssessmentDetailVo.setApproverDetailVos(collect);
                });
                List<String> flowIds = hkAssessmentScoreGroupByUsers.stream()
                        .collect(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(HkAssessmentScoreGroupByUser::getFlowTemplateId))))
                        .stream()
                        .map(HkAssessmentScoreGroupByUser::getFlowTemplateId).filter(flowId -> flowId != null && !flowId.isEmpty()).collect(Collectors.toList());
                String path = createChart(hkAssessmentScoreGroupByUsers,outPutAssessmentVo, hkAssessmentDetailVos, flowIds,approverDetailVo, hkAssessmentScoreCountData, userRoleCount, filename, response);
                downloadFile(path,response,filename);
            }
        }

    }
    public List<ApproverDetailVo> getApproverDetailVo(List<ApproverDetailVo> approverDetailVos){

        //这个存放处理后的数据
        List<ApproverDetailVo> handle = Lists.newArrayList();
        Map<String, List<ApproverDetailVo>> listGroup = approverDetailVos.stream().collect(groupingBy(ApproverDetailVo::getPid));

        listGroup.forEach((k,v) -> {
            if(v == null){
                //丢弃
                return;
            }else if (v.size() == 1){
                v.get(0).setTimeDifference(DateUtils.getDatePoor(v.get(0).getApprTime(),v.get(0).getApprTime()));
                handle.add(v.get(0));
            }else if (v.size() == 2){
                int size = v.size();
                Date createTime = null;
                for (int i = 0; i < v.size(); i++) {
                    if(i !=0 && i!= size){
                        v.get(i).setTimeDifference(DateUtils.getDatePoor(v.get(i).getApprTime(),createTime));
                        handle.add(v.get(i));
                    }
                    createTime = v.get(i).getApprTime();
                }
            }
            int size = v.size();
            //存放前一个节点的时间
            Date createTime = null;
            for (int i = 0; i < v.size(); i++) {
                if(i !=0 && i!= size-1){
                    v.get(i).setTimeDifference(DateUtils.getDatePoor(v.get(i).getApprTime(),createTime));
                    handle.add(v.get(i));
                }
                createTime = v.get(i).getApprTime();
            }

        });
        return handle;

    }

    public String createChart(List<HkAssessmentScoreGroupByUser> hkAssessmentScoreGroupByUsers,OutPutAssessmentVo outPutAssessmentVo,List<HkAssessmentDetailVo> hkAssessmentDetailVos,List<String> flowIds,List<ApproverDetailVo> approverDetailVo,List<HkAssessmentScoreCountDataVo> hkAssessmentScoreCountData,Map<String,Integer> userRoleCount,String filename,HttpServletResponse response) throws IOException {

        /**
         * 创建Workbook对象
         */
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("汇总表");
        worksheet.getCellRange("A1").setValue("序号");
        worksheet.getCellRange("B1").setValue("姓名");
        worksheet.getCellRange("C1").setValue("部门");
        worksheet.getCellRange("D1").setValue("岗位");
        worksheet.getCellRange("E1").setValue("考核结果");
        worksheet.getCellRange("F1").setValue("加分次数");
        worksheet.getCellRange("G1").setValue("扣分次数");
        worksheet.getCellRange("H1").setValue("共计次数");
        worksheet.getCellRange("I1").setValue("部门总人数");
        worksheet.getCellRange("J1").setValue("班长");
        worksheet.getCellRange("K1").setValue("员工");
        worksheet.getCellRange("L1").setValue("其他");
        worksheet.getCellRange("A" + (hkAssessmentScoreCountData.size() + 2)).setValue("合计");
        long worksheetrow=2;
        int worksheeti = 1;
        for (HkAssessmentScoreCountDataVo hkAssessmentScoreCountDataVo : hkAssessmentScoreCountData){
            worksheet.getCellRange("A"+worksheetrow).setValue(String.valueOf(worksheeti++));

            worksheet.getCellRange("B"+worksheetrow).setValue(hkAssessmentScoreCountDataVo.getNickName());

            worksheet.getCellRange("C"+worksheetrow).setValue(hkAssessmentScoreCountDataVo.getDepartmentTitle());

            worksheet.getCellRange("D"+worksheetrow).setValue(hkAssessmentScoreCountDataVo.getPostTitle());

            worksheet.getCellRange("E"+worksheetrow).setValue(String.valueOf(hkAssessmentScoreCountDataVo.getCountScore()));

            worksheet.getCellRange("F"+worksheetrow).setValue(String.valueOf(hkAssessmentScoreCountDataVo.getPlus()));

            worksheet.getCellRange("G"+worksheetrow).setValue(String.valueOf(hkAssessmentScoreCountDataVo.getMinus()));

            worksheet.getCellRange("H"+worksheetrow).setValue(String.valueOf(hkAssessmentScoreCountDataVo.getCountNumber()));

            worksheetrow++;
        }
        //分数总
        Double collect1 = hkAssessmentScoreCountData.stream().collect(Collectors.summingDouble(HkAssessmentScoreCountDataVo::getCountScore));
        Integer plusSum = hkAssessmentScoreCountData.stream().collect(Collectors.summingInt(HkAssessmentScoreCountDataVo::getPlus));
        Integer minusSum = hkAssessmentScoreCountData.stream().collect(Collectors.summingInt(HkAssessmentScoreCountDataVo::getMinus));
        Integer countSum = hkAssessmentScoreCountData.stream().collect(Collectors.summingInt(HkAssessmentScoreCountDataVo::getCountNumber));
        worksheet.getCellRange("E" + (hkAssessmentScoreCountData.size() + 2)).setValue(String.valueOf(collect1));
        worksheet.getCellRange("F" + (hkAssessmentScoreCountData.size() + 2)).setValue(String.valueOf(plusSum));
        worksheet.getCellRange("G" + (hkAssessmentScoreCountData.size() + 2)).setValue(String.valueOf(minusSum));
        worksheet.getCellRange("H" + (hkAssessmentScoreCountData.size() + 2)).setValue(String.valueOf(countSum));
        //人员
        worksheet.getCellRange("I2").setValue(String.valueOf(userRoleCount.get("all")));
        worksheet.getCellRange("J2").setValue(String.valueOf(userRoleCount.get("monitor")));
        worksheet.getCellRange("K2").setValue(String.valueOf(userRoleCount.get("staff")));
        worksheet.getCellRange("L2").setValue(String.valueOf(userRoleCount.get("other")));
        worksheet.setRowHeight(1,20);
        worksheet.getRange().get("A"+ (hkAssessmentScoreCountData.size() + 2) + ":" + LetterConvert.num2upper(4) +(hkAssessmentScoreCountData.size() + 2)).merge();
        CellRange worksheetRange1 = worksheet.getCellRange("A1:" + LetterConvert.num2upper(12) + "1");
        worksheetRange1.borderInside(LineStyleType.Thin, Color.black);
        worksheetRange1.borderAround(LineStyleType.Medium, Color.black);
        CellRange worksheetRange2 = worksheet.getCellRange("A2:" + LetterConvert.num2upper(8) +(hkAssessmentScoreCountData.size() + 2));
        worksheetRange2.borderInside(LineStyleType.Thin, Color.black);
        worksheetRange2.borderAround(LineStyleType.Medium, Color.black);
        worksheet.getAutoFilters().setRange(worksheet.getCellRange("A1:H" + (hkAssessmentScoreCountData.size() + 1)));
        //水平对齐居中
        worksheet.getAllocatedRange().getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        //垂直对齐方式设置为居中
        worksheet.getAllocatedRange().getCellStyle().setVerticalAlignment(VerticalAlignType.Center);


        Map<String, List<HkAssessmentScoreGroupByUser>> hkAssessmentScoreGroupByFlowTemplateIdList = hkAssessmentScoreGroupByUsers.stream().collect(groupingBy(HkAssessmentScoreGroupByUser::getFlowTemplateId));
        Map<String, List<HkAssessmentDetailVo>> hkAssessmentDetailVoByFlowTemplateIdList = hkAssessmentDetailVos.stream().collect(groupingBy(HkAssessmentDetailVo::getFlowTemplateId));
        Map<String, List<ApproverDetailVo>> approverDetailVoByFlowTemplateIdList = approverDetailVo.stream().collect(groupingBy(ApproverDetailVo::getFlowTemplateId));

        for (int i = 0; i < flowIds.size(); i++) {
            outPutAssessmentVo.setFlowTemplateId(flowIds.get(i));
            List<HkAssessmentScoreGroupByUser> hkAssessmentScoreGroupByUsers1 = iHkAssessmentScoreService.selectHkAssessmentScoreGroupByUserAndflowTemplateId(outPutAssessmentVo);
            setSheet(workbook,hkAssessmentScoreGroupByUsers1,hkAssessmentDetailVoByFlowTemplateIdList.get(flowIds.get(i)),approverDetailVoByFlowTemplateIdList.get(flowIds.get(i)),flowIds.get(i),i+1);
        }
        String s = RandomUtil.randomString(8);
        s = "output/" + filename;
        workbook.saveToFile(s, ExcelVersion.Version2016);

        return s;
    }

    public void setSheet(Workbook workbook,List<HkAssessmentScoreGroupByUser> hkAssessmentScoreGroupByUsers,List<HkAssessmentDetailVo> hkAssessmentDetailVos,List<ApproverDetailVo> approverDetailVo,String flowTemplateId,Integer index){
        /**
         * 获取第二个工作表
         */
        FlowTemplateinfo flowTemplateinfo = iFlowTemplateinfoService.getById(flowTemplateId);
        List<FlowNodeinfo> flowNodeInfo = iFlowNodeinfoService.getFlowNodeInfo(flowTemplateId, flowTemplateinfo.getTemplateVersion());
        Worksheet sheet = null;
        if (index > 2){
            sheet = workbook.getWorksheets().add(flowTemplateinfo.getTemplateName());
        }else {
            sheet = workbook.getWorksheets().get(index);
            sheet.setName(flowTemplateinfo.getTemplateName());
        }
        /**
         * TODO 将图表数据写入工作表
         */
        sheet.getCellRange("A1").setValue("序号");
        sheet.getCellRange("B1").setValue("姓名");
        sheet.getCellRange("C1").setValue("部门");
        sheet.getCellRange("D1").setValue("岗位");
        sheet.getCellRange("E1").setValue("考核结果");
        sheet.getCellRange("F1").setValue("考核详情");
        sheet.getCellRange("K1").setValue("审批记录");
        sheet.getCellRange("R1").setValue("审批流程");
        sheet.getCellRange("F2").setValue("考核日期");
        sheet.getCellRange("G2").setValue("项目类型");
        sheet.getCellRange("H2").setValue("指标名称");
        sheet.getCellRange("I2").setValue("考核指标");
        sheet.getCellRange("J2").setValue("考核分数");
        sheet.getCellRange("K2").setValue("提交人");
        sheet.getCellRange("L2").setValue("备注");
        sheet.getCellRange("M2").setValue("提交时间");
        sheet.getCellRange("N2").setValue("终审人");
        sheet.getCellRange("O2").setValue("备注");
        sheet.getCellRange("P2").setValue("终审时间");
        sheet.getCellRange("Q2").setValue("累计用时");

        List<FlowNodeinfo> flowNodeinfos = flowNodeInfo.stream().sorted(Comparator.comparing(FlowNodeinfo::getSetpId)).collect(Collectors.toList());

        if (flowNodeinfos.size() > 0){
            for (int i = 0; i < flowNodeinfos.size(); i++) {
                sheet.getCellRange(LetterConvert.num2upper(18 + i) + "2").setValue("第" + LetterConvert.getCNNum(i + 1) +"审批节点人：(" + flowNodeinfos.get(i).getNodeName() + ")");
            }
        }
        hkAssessmentScoreGroupByUsers = hkAssessmentScoreGroupByUsers.stream().sorted(Comparator.comparing(HkAssessmentScoreGroupByUser::getUserId).thenComparing(HkAssessmentScoreGroupByUser::getPid)).collect(Collectors.toList());

        long row=3;
        int i = 1;

        for (HkAssessmentScoreGroupByUser s:hkAssessmentScoreGroupByUsers){
            long count=hkAssessmentDetailVos.stream().filter(item->s.getUserId().equals(item.getUserId())).count();
            Map<String, Double> collect = hkAssessmentDetailVos.stream().collect(groupingBy(HkAssessmentDetailVo::getUserId, Collectors.summingDouble(HkAssessmentDetailVo::getScore)));

            sheet.getCellRange("A"+row).setValue(String.valueOf(i++));
            sheet.getRange().get("A"+row+":A"+(row+count-1)).merge();
            sheet.getCellRange("B"+row).setValue(s.getNickName());
            sheet.getRange().get("B"+row+":B"+(row+count-1)).merge();
            sheet.getCellRange("C"+row).setValue(s.getDepartmentTitle());
            sheet.getRange().get("C"+row+":C"+(row+count-1)).merge();
            sheet.getCellRange("D"+row).setValue(s.getPostTitle());
            sheet.getRange().get("D"+row+":D"+(row+count-1)).merge();
            sheet.getCellRange("E"+row).setValue(String.valueOf(collect.get(s.getUserId())));
            sheet.getRange().get("E"+row+":E"+(row+count-1)).merge();

            row+=count;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdft = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        long j=3;
        for (HkAssessmentDetailVo s:hkAssessmentDetailVos){
            String submit = sdft.format(s.getSubmitTime());
            String approval = sdft.format(s.getApprovalTime());
            sheet.getCellRange("F"+(j)).setValue(sdf.format(s.getAssessmentTime()));
            sheet.getCellRange("G"+(j)).setValue(s.getIndexType());
            sheet.getCellRange("H"+(j)).setValue(s.getIndexName());
            sheet.getCellRange("I"+(j)).setValue(s.getIndexDescribe());
            sheet.getCellRange("J"+(j)).setValue(String.valueOf(s.getScore()));
            sheet.getCellRange("K"+(j)).setValue(s.getSubmitUser());
            sheet.getCellRange("L"+(j)).setValue(s.getSubmitDescribe());
            sheet.getCellRange("M"+(j)).setValue(submit + ".");
            sheet.getCellRange("N"+(j)).setValue(s.getApprovalUserName());
            sheet.getCellRange("O"+(j)).setValue(s.getCommented());
            sheet.getCellRange("P"+(j)).setValue(approval + ".");
            sheet.getCellRange("Q"+(j)).setValue(s.getTimeDifference());
            j++;
        }
        Map<String, List<ApproverDetailVo>> collect = approverDetailVo.stream().collect(Collectors.groupingBy(ApproverDetailVo::getPid));
        List<HkAssessmentDetailVo> collect2 = hkAssessmentDetailVos.stream().collect(
                Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(
                        Comparator.comparing(HkAssessmentDetailVo::getPrId))), ArrayList::new));
        collect2 = collect2.stream().sorted(Comparator.comparing(HkAssessmentDetailVo::getUserId).thenComparing(HkAssessmentDetailVo::getPrId)).collect(Collectors.toList());




        long appRow=3;
        int detailIndex = 0;
        for (String map : collect.keySet()){
            long count=hkAssessmentDetailVos.stream().filter(item->map.equals(item.getPrId())).count();

            List<ApproverDetailVo> s = collect.get(collect2.get(detailIndex).getPrId());
            if (s.size() > 0){
                for (int i1 = 0; i1 < s.size(); i1++) {
                    Character character = LetterConvert.num2upper(18 + i1);
                    /*if (s.size() > 1){
                        String mergeNum =  character.toString() + appRow + ":" + character+((int)appRow+s.size()-1);
                        sheet.getRange().get(mergeNum).merge();
                        sheet.getCellRange( character.toString() + appRow).setValue("审核人：" + s.get(i1).getNickName()
                                + "\n备注" + s.get(i1).getIndexComment()
                                + "\n分值" + s.get(i1).getScore()
                                + "\n时间" + sdft.format(s.get(i1).getApprTime())
                                + "\n用时" + s.get(i1).getTimeDifference());
                        break;
                    }*/
                    sheet.getCellRange( character.toString() + appRow).setValue("审核人：" + s.get(i1).getNickName()
                            + "\n备注" + s.get(i1).getIndexComment()
                            + "\n分值" + s.get(i1).getScore()
                            + "\n时间" + sdft.format(s.get(i1).getApprTime())
                            + "\n用时" + s.get(i1).getTimeDifference());

                }

            }
            detailIndex++;
            appRow ++;
        }
        //合并单元格
        sheet.getRange().get("A1:A2").merge();
        sheet.getRange().get("B1:B2").merge();
        sheet.getRange().get("C1:C2").merge();
        sheet.getRange().get("D1:D2").merge();
        sheet.getRange().get("E1:E2").merge();
        sheet.getRange().get("F1:J1").merge();
        sheet.getRange().get("K1:Q1").merge();
        if (flowNodeinfos.size() > 0) {
            sheet.getRange().get("R1:" + LetterConvert.num2upper(18 + flowNodeinfos.size() - 1) + "1").merge();
        }
        //设置行高
        sheet.setRowHeight(1,25);
        sheet.setRowHeight(2,20);

        if (flowNodeinfos.size() > 0) {
            CellRange range1 = sheet.getCellRange("A1:" + LetterConvert.num2upper(18 + flowNodeinfos.size() - 1) + (hkAssessmentDetailVos.size() + 2));
            range1.borderInside(LineStyleType.Thin, Color.black);
            range1.borderAround(LineStyleType.Medium, Color.black);
        }else {
            CellRange range1 = sheet.getCellRange("A1:" + LetterConvert.num2upper(18) +  (hkAssessmentDetailVos.size() + 2));
            range1.borderInside(LineStyleType.Thin, Color.black);
            range1.borderAround(LineStyleType.Medium, Color.black);
        }

        sheet.setColumnWidth(6, 20);
        sheet.setColumnWidth(8, 20);
        sheet.setColumnWidth(11, 20);
        sheet.setColumnWidth(12, 20);
        sheet.setColumnWidth(13, 20);
        sheet.setColumnWidth(14, 20);
        sheet.setColumnWidth(15, 20);
        sheet.setColumnWidth(16, 20);
        sheet.setColumnWidth(17, 20);
        sheet.setColumnWidth(18, 35);
        sheet.setColumnWidth(19, 35);
        sheet.getAutoFilters().setRange(sheet.getCellRange("A1:J" + hkAssessmentDetailVos.size() + 2));
        //水平对齐居中
        sheet.getAllocatedRange().getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        //垂直对齐方式设置为居中
        sheet.getAllocatedRange().getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
    }

    public static void downloadFile( String path,HttpServletResponse response, String filename) throws UnsupportedEncodingException {
        File file = new File(path);
        response.setContentType("application/msexcel");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1"));
        byte[] buffer = new byte[1024];
        FileInputStream fis = null; //文件输入流
        BufferedInputStream bis = null;

        OutputStream os = null; //输出流
        try {
            os = response.getOutputStream();
            fis = new FileInputStream(file);
            bis = new BufferedInputStream(fis);
            int y = bis.read(buffer);
            while (y != -1) {
                os.write(buffer);
                y = bis.read(buffer);
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            bis.close();
            fis.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
//删除缓存和文件
        file.delete();
    }

    //多个文件压缩成压缩包并下载
    public void zipFiles(List<File> fileList,HttpServletResponse httpResponse) {
        FileInputStream fileInputStream = null;
        try(ZipOutputStream zipOutputStream = new ZipOutputStream(httpResponse.getOutputStream()); OutputStream out =null) {
            //下载压缩包
            httpResponse.setContentType("application/zip");
            httpResponse.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("绩效考核.zip", "UTF-8"));
            // 创建 ZipEntry 对象
            for (File file:fileList){
                fileInputStream = new FileInputStream(file);
                // 实例化 ZipEntry 对象，源文件数组中的当前文件
                ZipEntry zipEntry = new ZipEntry(file.getName());
                zipOutputStream.putNextEntry(zipEntry);
                // 该变量记录每次真正读的字节个数
                int len;
                // 定义每次读取的字节数组
                byte[] buffer = new byte[1024];
                while ((len = fileInputStream.read(buffer)) > 0) {
                    zipOutputStream.write(buffer, 0, len);
                }
                zipOutputStream.flush();
                fileInputStream.close();
                file.delete();
            }

        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }


    /**
     * 员工考核明细表1.6
     */
    @RequestMapping(value = "/getHkOverTimeGroupByUser", method = RequestMethod.GET)
    @ResponseBody
    public void getHkOverTimeGroupByUser(String departmentId, String beginDate, String endDate,HttpServletResponse response) throws Exception {
        HkOvertimeByPageVo hkOvertimeByPageVo = new HkOvertimeByPageVo();
        hkOvertimeByPageVo.setDepartmentId(departmentId);
        hkOvertimeByPageVo.setStartTime(beginDate);
        hkOvertimeByPageVo.setEndTime(endDate);
        List<HkOvertimeInfoNameAndUserNickVo> hkOvertimeInfoAndUserNickNameByOutput = hkOvertimeDetailService.getHkOvertimeInfoAndUserNickNameByOutput(hkOvertimeByPageVo);
        if (hkOvertimeInfoAndUserNickNameByOutput.size() == 0){
            throw new Exception("暂无数据");
        }

        List<File> fileList = new ArrayList<>();
        String[] departmentIds = departmentId.split(",");
        if (departmentIds.length > 1){
            for (int i = 0; i < departmentIds.length; i++) {
                HontDepartment hontDepartment1 = hontDepartmentService.get(departmentIds[i]);
                String filename = hontDepartment1.getTitle() + "加班/调休_统计表.xlsx";
                String chart = createChartByOverTime( hkOvertimeInfoAndUserNickNameByOutput,departmentId,beginDate,filename, response);
                fileList.add(new File(chart));
            }
            zipFiles(fileList,response);
        }else if (departmentIds.length == 1){
            HontDepartment hontDepartment1 = hontDepartmentService.get(departmentIds[0]);
            String filename = hontDepartment1.getTitle() + "加班/调休_统计表.xlsx";
            String chart = createChartByOverTime( hkOvertimeInfoAndUserNickNameByOutput,departmentId,beginDate,filename, response);
            downloadFile(chart,response,filename);
        }

    }


    public String createChartByOverTime(List<HkOvertimeInfoNameAndUserNickVo> hkOvertimeInfoAndUserNickNameByOutput,String departmentId,String beginDate,String filename,HttpServletResponse response) throws IOException {

        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("加班明细表");
        worksheet.getCellRange("A1").setValue("序号");
        worksheet.getCellRange("B1").setValue("日期");
        worksheet.getCellRange("C1").setValue("姓名");
        worksheet.getCellRange("D1").setValue("加班原因");
        worksheet.getCellRange("E1").setValue("加班计时（小时）");
        worksheet.getCellRange("F1").setValue("加班时间");

        List<HkOvertimeInfoNameAndUserNickVo> overtimeInfoNameAndUserNickVos = hkOvertimeInfoAndUserNickNameByOutput.stream()
                .filter(obj -> obj.getFlowType().equals("2"))
                .collect(Collectors.toList());
        long worksheetrow=2;
        int worksheeti = 1;
        for (HkOvertimeInfoNameAndUserNickVo hkOvertimeInfoNameAndUserNickVo : overtimeInfoNameAndUserNickVos){
            worksheet.getCellRange("A"+worksheetrow).setValue(String.valueOf(worksheeti++));

            worksheet.getCellRange("B"+worksheetrow).setValue(hkOvertimeInfoNameAndUserNickVo.getCreateTime());

            worksheet.getCellRange("C"+worksheetrow).setValue(hkOvertimeInfoNameAndUserNickVo.getUserNickName());

            worksheet.getCellRange("D"+worksheetrow).setValue(hkOvertimeInfoNameAndUserNickVo.getCause());

            worksheet.getCellRange("E"+worksheetrow).setValue(hkOvertimeInfoNameAndUserNickVo.getTotalHours());

            worksheet.getCellRange("F"+worksheetrow).setValue(hkOvertimeInfoNameAndUserNickVo.getOverStartTime() + "-" + hkOvertimeInfoNameAndUserNickVo.getOverEndTime());

            worksheetrow++;
        }


        Worksheet worksheet1 = workbook.getWorksheets().get(1);
        worksheet1.setName("调休明细表");
        worksheet1.getCellRange("A1").setValue("序号");
        worksheet1.getCellRange("B1").setValue("日期");
        worksheet1.getCellRange("C1").setValue("姓名");
        worksheet1.getCellRange("D1").setValue("调休原因");
        worksheet1.getCellRange("E1").setValue("调休计时（小时）");
        worksheet1.getCellRange("F1").setValue("调休时间");

        List<HkOvertimeInfoNameAndUserNickVo> daysOffInfoNameAndUserNickVos = hkOvertimeInfoAndUserNickNameByOutput.stream()
                .filter(obj -> obj.getFlowType().equals("3"))
                .collect(Collectors.toList());
        long worksheetrowT=2;
        int worksheetiT = 1;
        for (HkOvertimeInfoNameAndUserNickVo hkOvertimeInfoNameAndUserNickVo : daysOffInfoNameAndUserNickVos){
            worksheet1.getCellRange("A"+worksheetrowT).setValue(String.valueOf(worksheetiT++));

            worksheet1.getCellRange("B"+worksheetrowT).setValue(hkOvertimeInfoNameAndUserNickVo.getCreateTime());

            worksheet1.getCellRange("C"+worksheetrowT).setValue(hkOvertimeInfoNameAndUserNickVo.getUserNickName());

            worksheet1.getCellRange("D"+worksheetrowT).setValue(hkOvertimeInfoNameAndUserNickVo.getCause());

            worksheet1.getCellRange("E"+worksheetrowT).setValue(hkOvertimeInfoNameAndUserNickVo.getTotalHours());

            worksheet1.getCellRange("F"+worksheetrowT).setValue(hkOvertimeInfoNameAndUserNickVo.getOverStartTime() + "-" + hkOvertimeInfoNameAndUserNickVo.getOverEndTime());

            worksheetrowT++;
        }

        List<HkOvertimeSummarizingAll> hkOvertimeSummarizingAlls = new ArrayList<>();
        Integer beforeMonthNum = DateUtils.getBeforeMonthNum();
        Integer theSameMonthNum = DateUtils.getTheSameMonthNum();
        String yearMonthNum = DateUtils.getYearMonthNum();
        String sameTimeStr = DateUtils.sameTimeStr(beginDate);
        String lastYearMonthNum = DateUtils.getLastYearMonthNum(beginDate);
        //本月
        QueryWrapper<HkOvertimeCollect> queryWrapper = Wrappers.query();
        queryWrapper.eq("date_moth",sameTimeStr);
        queryWrapper.like("department_id","%"+departmentId);
        List<HkOvertimeCollect> hkOvertimeCollects = hkOvertimeCollectMapper.selectList(queryWrapper);

        //上月
        QueryWrapper<HkOvertimeCollect> collectQueryWrapper = Wrappers.query();
        collectQueryWrapper.eq("date_moth",lastYearMonthNum);
        collectQueryWrapper.like("department_id","%"+departmentId);
        List<HkOvertimeCollect> selectOnes = hkOvertimeCollectMapper.selectList(collectQueryWrapper);

        List<String> same = hkOvertimeCollects.stream().map(HkOvertimeCollect::getHontUserId).collect(Collectors.toList());
        List<String> last = selectOnes.stream().map(HkOvertimeCollect::getHontUserId).collect(Collectors.toList());
        Set<String> set = new HashSet<>();
        set.addAll(last);
        set.addAll(same);
        List<String> mergedList = new ArrayList<>(set);
        for (String value : mergedList) {
            HkOvertimeSummarizingAll hkOvertimeSummarizingAll = new HkOvertimeSummarizingAll();
            HontUser byId = hontUserService.findById(value);
            hkOvertimeSummarizingAll.setNickName(byId.getNickName());
            hkOvertimeSummarizingAll.setUserId(value);
            hkOvertimeSummarizingAlls.add(hkOvertimeSummarizingAll);
        }

        hkOvertimeSummarizingAlls.stream().map(m -> {
            m.setTime(yearMonthNum);
            selectOnes.stream().filter(m1 -> Objects.equals(m.getUserId(), m1.getHontUserId())).forEach(m1 -> {
                //上月剩余
                m.setBeforeMoth(m1.getCurrentmonthSurplusHours());
            });

            hkOvertimeCollects.stream().filter(m2 -> Objects.equals(m.getUserId(), m2.getHontUserId())).forEach(m2 -> {
                //本月总加班
                m.setOvertimeSameMoth(m2.getOvertimeTotalHours());
                m.setOvertimeSameMothTime(m2.getOvertimeTotalHours());
                //本月已休
                m.setUsedDay(m2.getDaysoffTotalHours());
            });
            return m;
        }).collect(Collectors.toList());

        hkOvertimeSummarizingAlls.forEach(m -> {
            m.setTotalDay(m.getBeforeMoth() + m.getOvertimeSameMoth());
            m.setResidueDay((m.getBeforeMoth() + m.getOvertimeSameMoth()) - m.getUsedDay());
        });

        log.info("汇总数据:{}",hkOvertimeSummarizingAlls);

        Worksheet worksheet2 = workbook.getWorksheets().get(2);
        worksheet2.setName("汇总表");
        worksheet2.getCellRange("A1").setValue("序号");
        worksheet2.getCellRange("B1").setValue("日期");
        worksheet2.getCellRange("C1").setValue("姓名");
        worksheet2.getCellRange("D1").setValue(beforeMonthNum + "月余（天）");
        worksheet2.getCellRange("E1").setValue(theSameMonthNum + "月加班总计（小时）");
        worksheet2.getCellRange("F1").setValue(theSameMonthNum + "月折合天数");
        worksheet2.getCellRange("G1").setValue("合计天数");
        worksheet2.getCellRange("H1").setValue("已休天数");
        worksheet2.getCellRange("I1").setValue("余天数");

        long worksheetrowS=2;
        int worksheetiS = 1;
        for (HkOvertimeSummarizingAll hkOvertimeInfoNameAndUserNickVo : hkOvertimeSummarizingAlls){
            worksheet2.getCellRange("A"+worksheetrowS).setValue(String.valueOf(worksheetiS++));

            worksheet2.getCellRange("B"+worksheetrowS).setValue(hkOvertimeInfoNameAndUserNickVo.getTime());

            worksheet2.getCellRange("C"+worksheetrowS).setValue(hkOvertimeInfoNameAndUserNickVo.getNickName());

            worksheet2.getCellRange("D"+worksheetrowS).setValue(DateUtils.getTransition(hkOvertimeInfoNameAndUserNickVo.getBeforeMoth()));

            worksheet2.getCellRange("E"+worksheetrowS).setValue(String.valueOf(hkOvertimeInfoNameAndUserNickVo.getOvertimeSameMoth()));

            worksheet2.getCellRange("F"+worksheetrowS).setValue(DateUtils.getTransition(hkOvertimeInfoNameAndUserNickVo.getOvertimeSameMothTime()));

            worksheet2.getCellRange("G"+worksheetrowS).setValue(DateUtils.getTransition(hkOvertimeInfoNameAndUserNickVo.getTotalDay()));

            worksheet2.getCellRange("H"+worksheetrowS).setValue(DateUtils.getTransition(hkOvertimeInfoNameAndUserNickVo.getUsedDay()));

            worksheet2.getCellRange("I"+worksheetrowS).setValue(DateUtils.getTransition(hkOvertimeInfoNameAndUserNickVo.getResidueDay()));

            worksheetrowS++;
        }
        //sheet1
        worksheet.setColumnWidth(3,25);
        worksheet.setColumnWidth(4,25);
        worksheet.setColumnWidth(5,25);
        //sheet2
        worksheet1.setColumnWidth(3,25);
        worksheet1.setColumnWidth(4,25);
        worksheet1.setColumnWidth(5,25);
        //sheet3
        worksheet2.setColumnWidth(4,25);
        worksheet2.setColumnWidth(5,25);
        worksheet2.setColumnWidth(6,25);
        worksheet2.setColumnWidth(7,25);
        worksheet2.setColumnWidth(9,25);

        String s = RandomUtil.randomString(8);
        s = "output/" + filename;
        workbook.saveToFile(s, ExcelVersion.Version2016);

        return s;
    }

}
